package com.aishang.excelform.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author cmp
 */
@Slf4j
public class AnalyExcelUil {
    private static Map<String, Object>[] map;
    private static final String SHEET_PREFIX = "sheet";
    private static final String CALLBACK = "_callback()";
    private static final String GET_EXCEL_CELL_VALUE = "getExcelCellValue";
    private static final String CELL_VALUE_FORMATTING = "cellValueFormatting";
    private static final String SINGLE_QUOTE = "'";
    public static String readExcelToHtml(String filePath) {
        InputStream is = null;
        String htmlExcel = null;
        try {
            File sourcefile = new File(filePath);
            is = new FileInputStream(sourcefile);
            Workbook wb = WorkbookFactory.create(is);
            htmlExcel = readWorkbook(wb, true);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return htmlExcel;
    }

    public static String readExcelToHtml(InputStream is) {
        String htmlExcel = null;
        try {
            Workbook wb = WorkbookFactory.create(is);
            htmlExcel = readWorkbook(wb, true);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return htmlExcel;
    }

    /**
     * 根据excel的版本分配不同的读取方法进行处理
     *
     * @param wb
     * @param isWithStyle
     * @return
     */
    private static String readWorkbook(Workbook wb, boolean isWithStyle) {
        String htmlExcel = "";
        if (wb instanceof XSSFWorkbook) {
            XSSFWorkbook xWb = (XSSFWorkbook) wb;
            htmlExcel = getExcelInfo(xWb, isWithStyle);
        } else if (wb instanceof HSSFWorkbook) {
            HSSFWorkbook hWb = (HSSFWorkbook) wb;
            htmlExcel = getExcelInfo(hWb, isWithStyle);
        }
        return htmlExcel;
    }

    /**
     * 读取excel成string
     *
     * @param wb
     * @param isWithStyle
     * @return
     */
    public static String getExcelInfo(Workbook wb, boolean isWithStyle) {
        StringBuffer tabs = new StringBuffer();
        StringBuffer sb = new StringBuffer();
        sb.append("<div class=\"el-tabs__content\">");
        HashMap<Integer, String> sheets = new HashMap<>();
        sb.append("<link rel=\"stylesheet\" href=\"./index.css\"><script src=\"./d3.js\"></script><script src=\"./tabExchange.js\"></script><script src=\"./excelFunc.js\"></script>");
        tabs.append("<div id=\"excelTable\" class=\"el-tabs el-tabs--top el-tabs--border-card\"><div class=\"el-tabs__header is-top\"><div class=\"el-tabs__nav-wrap is-top\"><div class=\"el-tabs__nav-scroll\"><div role=\"tablist\" class=\"el-tabs__nav is-top\" style=\"transform: translateX(0px);\">");
        int sheetNum = 0;
        try{
            do{
                Sheet sheet = wb.getSheetAt(sheetNum);
                String sheetName = sheet.getSheetName();
                sheets.put(sheetNum, sheetName);
                String isActive = sheetNum == 0 ? " is-active" : "";
                String isSelected = sheetNum == 0 ? " aria-selected=\"true\"" : "";
                tabs.append("<div id=\"tab-0\" aria-controls=\"pane-0\" role=\"tab\" tabindex=\"-1\" class=\"el-tabs__item is-top" + isActive + "\" " + isSelected + ">");
                tabs.append(sheetName);
                tabs.append("</div>");
                // map等待存储excel图片
                Map<String, PictureData> sheetIndexPicMap = getSheetPictrues(sheetNum, sheet, wb);
                //临时保存位置，正式环境根据部署环境存放其他位置
                try {
                    if (sheetIndexPicMap != null) {
                        printImg(sheetIndexPicMap);
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
                //读取excel拼装html
                int lastRowNum = sheet.getLastRowNum();
                map = getRowSpanColSpanMap(sheet);
                int tableWidth = getTotalWidth(sheet);
                String dispaly = sheetNum == 0 ? "" : "display: none;";
                String isHiden = sheetNum == 0 ? "" : "aria-hidden=\"true\"";
                sb.append("<div role=\"tabpanel\" id=\"pane-1\" aria-labelledby=\"tab-1\" class=\"el-tab-pane\" style=\"" + dispaly + "\" " + isHiden + ">");
                sb.append("<table id='" + SHEET_PREFIX + sheetNum +"' style='border-collapse:collapse;table-layout: fixed;width: "+columnWidthChangePt(tableWidth)+"pt;'>");
                //table-layout为fixed时宽度会被第一行单元格固定，需要先增加colgroup固定每列宽度
                sb.append("<colgroup>");
                int totalColumn = sheet.getRow(0).getLastCellNum();
                for(int i = 0;i<totalColumn;i++){
                    sb.append("<col style='width:"+columnWidthChangePt(sheet.getColumnWidth(i))+"pt'></col>");
                }
                sb.append("</colgroup>");

                Row row = null;
                Cell cell = null;
                for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
                    if (rowNum > 1000) {
                        break;
                    }
                    row = sheet.getRow(rowNum);
                    int lastColNum = AnalyExcelUil.getColsOfTable(sheet)[0];
                    int rowHeight = AnalyExcelUil.getColsOfTable(sheet)[1];
                    if (null != row) {
                        lastColNum = row.getLastCellNum();
                        rowHeight = row.getHeight();
                    }
                    if (null == row) {
                        sb.append("<tr><td >  </td></tr>");
                        continue;
                    } else if (row.getZeroHeight()) {
                        continue;
                    } else if (0 == rowHeight) {
                        continue;     //针对jxl的隐藏行（此类隐藏行只是把高度设置为0，单getZeroHeight无法识别）
                    }
                    sb.append("<tr>");
                    sb = getTr(lastColNum, sheet, rowNum, cell, row, sheetIndexPicMap, sb, rowHeight, wb, isWithStyle);
                    sb.append("</tr>");
                    continue;
                }
                sb.append("</table>");
                sb.append("</div>");
                sheetNum++;
            }while(true);
        }catch(IllegalArgumentException e){

        }
        tabs.append("</div></div></div></div>");
        tabs.append(sb).append("</div></div>");
        return tabs.toString();
    }

    /**
     *获取tr
     * @param lastColNum
     * @param sheet
     * @param rowNum
     * @param cell
     * @param row
     * @param sheetIndexPicMap
     * @param sb
     * @param rowHeight
     * @param wb
     * @param isWithStyle
     * @return
     */
    private static StringBuffer getTr(int lastColNum, Sheet sheet, int rowNum, Cell cell, Row row, Map<String, PictureData> sheetIndexPicMap,
                                           StringBuffer sb, int rowHeight, Workbook wb, Boolean isWithStyle) {
        for (int colNum = 0; colNum < lastColNum; colNum++) {
            if (sheet.isColumnHidden(colNum)) {
                continue;
            }
            String imageRowNum = "0_" + rowNum + "_" + colNum;
            String imageHtml = "";
            cell = getMergedRegionCell(sheet,rowNum,colNum);
            Boolean exitTd = (sheetIndexPicMap != null && !sheetIndexPicMap.containsKey(imageRowNum) || sheetIndexPicMap == null) && cell == null;
            if (exitTd) {
                //特殊情况 空白的单元格会返回null+//判断该单元格是否包含图片，为空时也可能包含图片
                sb.append("<td>  </td>");
                continue;
            }
            if (sheetIndexPicMap != null && sheetIndexPicMap.containsKey(imageRowNum)) {
                //待修改路径
                String imagePath = "D:\\pic" + imageRowNum + ".jpeg";
                imageHtml = "<img src='" + imagePath + "' style='height:" + rowHeight / 20 + "px;'>";
            }
            String stringValue = getCellValue(cell);
            if (map[0].containsKey(rowNum + "," + colNum)) {
                String pointString = (String) map[0].get(rowNum + "," + colNum);
                int bottomeRow = Integer.valueOf(pointString.split(",")[0]);
                int bottomeCol = Integer.valueOf(pointString.split(",")[1]);
                int rowSpan = bottomeRow - rowNum + 1;
                int colSpan = bottomeCol - colNum + 1;
                if (map[2].containsKey(rowNum + "," + colNum)) {
                    rowSpan = rowSpan - (Integer) map[2].get(rowNum + "," + colNum);
                }
                if (map[4].containsKey(rowNum + "," + colNum)){
                    colSpan = colSpan + (Integer) map[4].get(rowNum + "," + colNum);
                }
                sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' ");
                if (map.length > 3 && map[3].containsKey(rowNum + "," + colNum)) {
                    //此类数据首行被隐藏，value为空，需使用其他方式获取值
                    stringValue = getMergedRegionValue(sheet, rowNum, colNum);
                }
            } else if (map[1].containsKey(rowNum + "," + colNum)) {
                map[1].remove(rowNum + "," + colNum);
                continue;
            } else {
                sb.append("<td ");
            }
            //判断是否需要样式
            if (isWithStyle) {
                //处理单元格样式
                dealExcelStyle(wb, sheet, cell, sb);
            }
            sb.append(">");
            dealFontStyle(wb, sheet, cell, sb);
            sb.append(">");
            if (sheetIndexPicMap != null && sheetIndexPicMap.containsKey(imageRowNum)) {
                sb.append(imageHtml);
            }
            //回车符用br代替
            stringValue = stringValue.replace("\r\n","<br>").replace("\n","<br>");
            if (stringValue == null || "".equals(stringValue.trim())) {
                sb.append("   ");
            } else {
                // 将ascii码为160的空格转换为html下的空格（ ）
                sb.append(stringValue.replace(String.valueOf((char) 160), " "));
            }
            sb.append("</font>");
            sb.append("</td>");
        }
        return sb;
    }

    /**
     * 分析excel表格，记录合并单元格相关的参数，用于之后html页面元素的合并操作
     *
     * @param sheet
     * @return
     */
    private static Map<String, Object>[] getRowSpanColSpanMap(Sheet sheet) {
        Map<String, String> map0 = new HashMap<String, String>(16);
        //保存合并单元格的对应起始和截止单元格
        Map<String, String> map1 = new HashMap<String, String>(16);
        //保存被合并的那些单元格
        Map<String, Integer> map2 = new HashMap<String, Integer>(16);
        //记录被隐藏的单元格个数
        Map<String, String> map3 = new HashMap<String, String>(16);
        //记录合并了单元格，但是合并的首行被隐藏的情况
        Map<String, Integer> map4 = new HashMap<String, Integer>(16);
        //记录被隐藏的列个数
        int mergedNum = sheet.getNumMergedRegions();
        CellRangeAddress range = null;
        Row row = null;
        for (int i = 0; i < mergedNum; i++) {
            range = sheet.getMergedRegion(i);
            int topRow = range.getFirstRow();
            int topCol = range.getFirstColumn();
            int bottomRow = range.getLastRow();
            int bottomCol = range.getLastColumn();
            /**
             * 此类数据为合并了单元格的数据
             * 1.处理隐藏（只处理行隐藏，列隐藏poi已经处理）
             */
            if (topRow != bottomRow) {
                int zeroRoleNum = 0;
                int tempRow = topRow;
                for (int j = topRow; j <= bottomRow; j++) {
                    row = sheet.getRow(j);
                    if (row.getZeroHeight() || row.getHeight() == 0) {
                        if (j == tempRow) {
                            //首行就进行隐藏，将rowTop向后移
                            tempRow++;
                            continue;//由于top下移，后面计算rowSpan时会扣除移走的列，所以不必增加zeroRoleNum;
                        }
                        zeroRoleNum++;
                    }
                }
                if (tempRow != topRow) {
                    map3.put(tempRow + "," + topCol, topRow + "," + topCol);
                    topRow = tempRow;
                }
                if (zeroRoleNum != 0) {
                    map2.put(topRow + "," + topCol, zeroRoleNum);
                }
            }
            map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
            int tempRow = topRow;
            while (tempRow <= bottomRow) {
                int tempCol = topCol;
                while (tempCol <= bottomCol) {
                    map1.put(tempRow + "," + tempCol, topRow + "," + topCol);
                    tempCol++;
                }
                tempRow++;
            }
            //如果首列隐藏，则后移
            int hidden = 0;
            while (sheet.isColumnHidden(topCol)){
                map0.remove(topRow + "," + topCol);
                topCol++;
                hidden++;
            }
            if(hidden!=0){
                map1.remove(topRow + "," + topCol);
                if(!map0.containsKey(topRow + "," + topCol)){
                    map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
                }
                if(!map3.containsKey(topRow + "," + topCol)){
                    map3.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
                }
                map4.put(topRow + "," + topCol, hidden);
            }
        }
        Map[] map = {map0, map1, map2, map3,map4};
        return map;
    }


    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);

                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }
    /**
     * 获取合并单元格的cell
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static Cell getMergedRegionCell(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);

                    return fCell;
                }
            }
        }
        return sheet.getRow(row).getCell(column);
    }

    /**
     * 获取表格单元格Cell内容
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String result;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat(ConstantClassField.HMM)) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == ConstantClassField.NUM58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = DateUtil
                            .getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if (ConstantClassField.GENERAL.equals(temp)) {
                        format.applyPattern("#");
                    }
                    result = format.format(value);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                // String类型
                result = cell.getRichStringCellValue().toString();
                result = getFunctionHtmlReplacement(result, genSheetName(cell), getCellSignature(cell));
                break;
            case Cell.CELL_TYPE_FORMULA:
//                log.info("公式转换前: {}", cell.getCellFormula());
                result = getFormulaString(cell);
                break;
            default:
                result = "";
                break;
        }
        return result;
    }

    /**
     * 获取sheetName，格式如 sheet0
     * @param cell
     * @return
     */
    private static String genSheetName(Cell cell){
        Sheet sheet = cell.getSheet();
        Workbook workbook = sheet.getWorkbook();
        return SHEET_PREFIX + workbook.getSheetIndex(sheet.getSheetName());
    }

    private static String getCellSignature(Cell cell){
        return getCellSignature(cell.getRowIndex(), cell.getColumnIndex());
    }

    /**
     * 行列号转为 cell , 如：0,0 转为 A1
     * @param rowIndex
     * @param columnIndex
     * @return
     */
    private static String getCellSignature(int rowIndex, int columnIndex){
        StringBuffer temp = new StringBuffer();
        int t = columnIndex;
        do {
            if(t<26){
                temp.append((char) (t + 65));
            }
            t /= 26;
        }while(t > 26);
        //CellReference.convertNumToColString(columnIndex);//列号转换为字母可用工具类
        return temp.toString() + ++rowIndex + "";
    }

    /**
     * 受监听的cell，转换为input
     * @param tag
     * @return
     */
    public static String getFunctionHtmlReplacement(String tag, String sheetName, String cellName){
        String htmlReplacement = tag;
        if (tag.contains(HtmlDataUtil.ONEINPUTINPUTEVENT)) {
            String classTag = (tag.contains(HtmlDataUtil.MUST)) ? "class=\"must\" " : "";
            String disabledTag = (tag.contains(HtmlDataUtil.DISABLED)) ? "disabled=\"true\" " : "";
            String maxLengthTag = "";
            Pattern p = Pattern.compile(HtmlDataUtil.MAXLENGTH);
            Matcher m = p.matcher(tag);
            if (m.find()) {
                maxLengthTag = "maxlength=\"" + m.group(1) + "\"";
            }
            String id = sheetName + "_" + cellName;
            htmlReplacement = "<input " + classTag + disabledTag + maxLengthTag + " cell_type='num' value=\"\""
                    + " placeholder=\"\" style=\"width: 100%;height:100%; border:none;padding-left:5px\"  />";
        }
        return htmlReplacement;
    }
    /**
     * 获取有公式的cell,转换为input,并将公式转为js
     * @param cell
     * @return
     */
    private static String getFormulaString(Cell cell){
        String sheetName = genSheetName(cell);
        StringBuffer temp = new StringBuffer();
        String formula = cell.getCellFormula();
        //cell 的编号，如：A1
        String signature = getCellSignature(cell.getRowIndex(),cell.getColumnIndex());
        //添加span，用于公式计算结果的展示， id 为 sheet,cell 共同标识, 如：sheet0_A1
        String id = sheetName + "_" + signature;
        temp.append("<input onfocus=\"this.blur()\" style=\"width: 100%; border:none;padding-left:5px\" class='excel-formula' pattern='(\\d+)|(\\d+.\\d{1,2})'/>");
        //公式转换,并添加到js
        temp.append(analyseFormula(cell, formula));
        return temp.toString();
    }

    /**
     * 将公式转为js函数,只支持sum
     * @param formula
     * @return
     */
    public static String analyseFormula(Cell cell, String formula){
        String jsStart = "<script type='text/javascript'>";
        String jsEnd = "</script>";
        StringBuffer listenerBuffer = new StringBuffer("setTimeout(function(){");
        Sheet sheet = cell.getSheet();
        Workbook workbook = sheet.getWorkbook();
        ArrayList<String> sheetNames = getSheetNames(workbook);
        String sheetName = sheet.getSheetName();
        String currentSheetNum = SHEET_PREFIX + workbook.getSheetIndex(sheetName);
        //1.去掉空白符，方法名转大写， sum->SUM
        String result = formattingFunc(formula);
        //2.格式化引入的区域，匹配举例 sheetName!A1:A2
        result = formattingRegion(result, sheetNames, sheetName, getCellSignature(cell), listenerBuffer);
        //3.格式化引入的单元格
        result = formattingImportedCell(result, sheetNames, currentSheetNum + "_" + getCellSignature(cell), listenerBuffer);
        //4.格式化普通单元格
        result = formattingCommonCell(result, currentSheetNum, cell, listenerBuffer);
        //5.格式化开头的单元格
        result = formattingForeheadCell(result, currentSheetNum, cell, listenerBuffer);

        String dataFormatString = cell.getCellStyle().getDataFormatString();
//        System.out.println("数据格式："+dataFormatString);
//        System.out.println("公式转换结果：" + result);
        //带公式的cell的回调函数
        String id = currentSheetNum + "_" + getCellSignature(cell);
        listenerBuffer.append(id + CALLBACK +"},1000);");
        String callBack = " function " + id + CALLBACK + "{var el=document.getElementById('" + id + "').children[0];el.value=" + result + ";el.value=" + CELL_VALUE_FORMATTING + "(el.value,'" + dataFormatString + "');var evt = document.createEvent('HTMLEvents'); evt.initEvent('input', true, false); el.dispatchEvent(evt);} ";
//        System.out.println("js回调结果：" + jsStart + listenerBuffer.toString() + callBack + jsEnd);
        return jsStart + listenerBuffer.toString() + callBack + jsEnd;
    }

    /**
     * 1.去掉空白符，方法名转大写， sum->SUM
     * @param input
     * @return
     */
    private static String formattingFunc(String input){
        //1.替换空白符
        input = input.replaceAll("\\s", "");
        //2.格式化函数，当前只有sum, count
        String regex = "((?i)sum)|((?i)count)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        while(matcher.find()){
            String ex = matcher.group(0);
            ex.toUpperCase();
            input = input.replaceAll(ex, ex.toUpperCase());
        }
        return input;
    }

    /**
     * 2.格式化区域，如：sheet1!A1:A2 -> 'sheet1','A1','A2'
     * @param input
     * @return
     */
    private static String formattingRegion(String input, ArrayList<String> sheetNames, String currentSheetName, String cellName, StringBuffer listenerBuffer){
        String regex = "(([^!\\(\\s]+)(!))?([A-Z]+\\d+):([A-Z]+\\d+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        while(matcher.find()){
            //当前sheet
            String currentSheetNum = SHEET_PREFIX + sheetNames.indexOf(formattingSheetName(currentSheetName));
            //区域所属的sheet，若没有引入其他 sheet 的 name，则表示区域属于当前sheet，用当前 sheet 的 name
            String importSheetName = matcher.group(2);
            String importSheetNum;
            if(importSheetName == null){
                importSheetNum = currentSheetNum;
            }else{
                importSheetNum = SHEET_PREFIX + sheetNames.indexOf(formattingSheetName(importSheetName));
            }
            String rangeStart = matcher.group(4);
            String rangeEnd = matcher.group(5);
            //区域转换
            input = matcher.replaceAll("'" + importSheetNum + "','" + rangeStart + "','" + rangeEnd + "'");
            //添加监听
            addListenerForRegion(importSheetNum, rangeStart, rangeEnd, currentSheetNum + "_" + cellName, listenerBuffer);
        }
        return input;
    }

    /**
     * 为区域添加监听事件
     * @param importSheetNum
     * @param rangeStart
     * @param rangeEnd
     * @param currentCellName
     * @return
     */
    private static String addListenerForRegion(String importSheetNum, String rangeStart, String rangeEnd, String currentCellName, StringBuffer listenerBuffer) {
        int[] startRowAndColIndex = getRowAndColIndex(rangeStart);
        int[] endRowAndColIndex = getRowAndColIndex(rangeEnd);
        for (int rowIndex = startRowAndColIndex[0] ; rowIndex <= endRowAndColIndex[0] ; rowIndex++){
            for (int colIndex = startRowAndColIndex[1] ; colIndex <= endRowAndColIndex[1] ; colIndex++){
                String cellName = CellReference.convertNumToColString(colIndex) + rowIndex;
                String sheetCellName = importSheetNum + "_" + cellName;
                String listener = currentCellName + "_" + sheetCellName;
                listenerBuffer.append(" var el=document.getElementById('" + sheetCellName + "');if(el&&el.children[0]){el.children[0].addEventListener('input'," + listener + ");}; ");
                listenerBuffer.append("function " + listener + "(e){" + currentCellName + CALLBACK  + "};");
            }
        }
        return null;
    }

    /**
     * 本剧cellName获取行列数
     * @param cellName
     * @return
     */
    private static int[] getRowAndColIndex(String cellName){
        String regex = "([A-Z]+)(\\d+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(cellName);
        matcher.find();
        int colIndex = CellReference.convertColStringToIndex(matcher.group(1));
        int rowIndex = Integer.parseInt(matcher.group(2));
        int[] ints = new int[2];
        ints[0] = rowIndex;
        ints[1] = colIndex;
        return ints;
    }

    /**
     * 3.格式化引入的cell，如：sheet!A1
     * @param input
     * @param sheetNames
     * @return
     */
    private static String formattingImportedCell(String input, ArrayList<String> sheetNames, String currentSheetName, StringBuffer listenerBuffer){
        String regex = "(([^!\\(+\\-*/\\s]+)(!))([A-Z]+\\d+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        while(matcher.find()){
            String g0 = matcher.group(0);
            String importSheetName = matcher.group(2);
            String importSheetNum = SHEET_PREFIX + sheetNames.indexOf(formattingSheetName(importSheetName));
            String cellName = matcher.group(4);
            String replace = importSheetNum + "_" + cellName;
            input = GET_EXCEL_CELL_VALUE + "('" +  input.replaceAll(g0,replace) + "')";
            addListenerForCell(importSheetNum, cellName, currentSheetName, listenerBuffer);
        }
        return input;
    }

    private static void addListenerForCell(String importSheetNum, String cellName, String currentCellName, StringBuffer listenerBuffer) {
        String sheetCellName = importSheetNum + "_" + cellName;
        String listener = currentCellName + "_" + sheetCellName;
        listenerBuffer.append(" var el=document.getElementById('" + sheetCellName + "');if(el&&el.children[0]){el.children[0].addEventListener('input'," + listener + ");}; ");
        listenerBuffer.append("function " + listener + "(e){" + currentCellName + CALLBACK  + "};");
    }

    /**
     * 4.格式化当前sheet中的cell
     * @param input
     * @param currentSheetNum
     * @return
     */
    private static String formattingCommonCell(String input, String currentSheetNum, Cell cell, StringBuffer listenerBuffer){
        String regex = "([^\\_'])([A-Z]+\\d+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        while(matcher.find()){
            String ex = matcher.group(0);
            ex = ex.replaceAll("\\+","\\\\+");
            ex = ex.replaceAll("\\*","\\\\*");
            String cellName = matcher.group(2);
            input = input.replaceAll(ex, matcher.group(1) +GET_EXCEL_CELL_VALUE + "('" +  currentSheetNum + "_" + cellName + "')");
            addListenerForCell(currentSheetNum, cellName, currentSheetNum + "_" + getCellSignature(cell) , listenerBuffer);
        }
        return input;
    }

    /**
     * 5.替换开头的普通cell
     * @param input
     * @param currentSheetNum
     * @return
     */
    private static String formattingForeheadCell(String input, String currentSheetNum, Cell cell, StringBuffer listenerBuffer){
        String regex = "^([A-Z]+\\d+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        while(matcher.find()){
            String replacement = GET_EXCEL_CELL_VALUE + "('" + currentSheetNum + "_" + matcher.group(0) + "')";
            input = input.replaceAll(matcher.group(0), replacement);
            addListenerForCell(currentSheetNum, matcher.group(0), currentSheetNum + "_" + getCellSignature(cell), listenerBuffer);
        }
        return input;
    }



    private static String getCellType(Cell cell){
        FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
        CellValue evaluateCell = evaluator.evaluate(cell);
        return null;
    }

    /**
     * 格式化sheetName
     * @param sheetName
     * @return
     */
    private static String formattingSheetName(String sheetName){
        if(sheetName==null){
            return null;
        }
        if(sheetName.indexOf(SINGLE_QUOTE)==0){
            //excel sheet名称的首尾不允许出现英文单引号，一旦出现需要替换掉
            int length = sheetName.length() - 1;
            sheetName = sheetName.substring(1, length);
        }
        return sheetName;
    }

    /**
     * 获取WorkBook 所有的sheet
     * @param workbook
     * @return
     */
    private static ArrayList<String> getSheetNames(Workbook workbook){
        int numberOfSheets = workbook.getNumberOfSheets();
        ArrayList<String> sheetNames = new ArrayList<>();
        for( int i = 0 ; i < numberOfSheets ; i++){
            sheetNames.add(workbook.getSheetAt(i).getSheetName());
        }
        return sheetNames;
    }

    /**
     * 处理表格样式
     *
     * @param wb
     * @param sheet
     * @param cell
     * @param sb
     */
    private static void dealExcelStyle(Workbook wb, Sheet sheet, Cell cell, StringBuffer sb) {
        CellStyle cellStyle = cell.getCellStyle();
        if (cellStyle != null) {
            short alignment = cellStyle.getAlignment();
            sb.append("align='" + convertAlignToHtml(alignment) + "' ");
            //单元格内容的水平对齐方式
            short verticalAlignment = cellStyle.getVerticalAlignment();
            sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' ");
            //单元格中内容的垂直排列方式
            if (wb instanceof XSSFWorkbook) {
                sb = getByXssf(sb, cellStyle, cell, sheet);
            } else if (wb instanceof HSSFWorkbook) {
                HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb);
                short fontColor = hf.getColor();
                sb.append("style='");
                HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
                // 类HSSFPalette用于求的颜色的国际标准形式
                HSSFColor hc = palette.getColor(fontColor);
                int topRow = cell.getRowIndex(), topColumn = cell.getColumnIndex();
                if (map[0].containsKey(topRow + ConstantClassField.COMMA + topColumn)) {
                    //该单元格为合并单元格，宽度需要获取所有单元格宽度后合并
                    String value = (String) map[0].get(topRow + "," + topColumn);
                    String[] ary = value.split(",");
                    int bottomColumn = Integer.parseInt(ary[1]);
                    int bottomRow = Integer.parseInt(ary[0]);
                    if (topColumn != bottomColumn) {
                        //合并列，需要计算相应宽度
                        int columnWidth = 0;
                        for (int i = topColumn; i <= bottomColumn; i++) {
                            columnWidth += sheet.getColumnWidth(i);
                        }
                        sb.append("width:" + columnWidthChangePt(columnWidth) + "pt;");
                    } else {
                        int columnWidth = sheet.getColumnWidth(topColumn);
                        sb.append("width:" + columnWidthChangePt(columnWidth) + "pt;");
                    }
                    if (topRow != bottomRow) {
                        //合并列，需要计算相应宽度
                        int rowHeight = 0;
                        for (int i = topRow; i <= bottomRow; i++) {
                            rowHeight += sheet.getRow(i).getHeight();
                        }
                        sb.append("Height:" + rowHeightChangePt(rowHeight) + "pt;");
                    } else {
                        int rowHeight = sheet.getRow(topRow).getHeight();
                        sb.append("Height:" + rowHeightChangePt(rowHeight) + "pt;");
                    }
                    sb.append(getLeftBorderStyle(sheet.getRow(topRow).getCell(topColumn).getCellStyle()));
                    sb.append(getRightBorderStyle(sheet.getRow(bottomRow).getCell(bottomColumn).getCellStyle()));
                } else {
                    int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());
                    int rowHeight = cell.getRow().getHeight();
                    sb.append("width:" + columnWidthChangePt(columnWidth) + "pt;");
                    sb.append("Height:" + rowHeightChangePt(rowHeight) + "pt;");
                    sb.append(getLeftBorderStyle(cellStyle));
                    sb.append(getRightBorderStyle(cellStyle));
                }
                short bgColor = cellStyle.getFillForegroundColor();
                hc = palette.getColor(bgColor);
                String bgColorStr = convertToStardColor(hc);
                if (bgColorStr != null && !"".equals(bgColorStr.trim())) {
                    sb.append("background-color:" + bgColorStr + ";");
                    // 背景颜色
                }
            }
            sb.append("' ");
        }
    }
    private static void dealFontStyle(Workbook wb, Sheet sheet, Cell cell, StringBuffer sb){
        String id = SHEET_PREFIX + wb.getSheetIndex(sheet) + "_" + getCellSignature(cell);
        sb.append("<font id='" + id + "' style=\"");
        CellStyle cellStyle = cell.getCellStyle();
        if(wb instanceof XSSFWorkbook){
            XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont();
            XSSFColor xc = xf.getXSSFColor();
            if (xc != null && !"".equals(xc.toString())) {
                sb.append("color:#" + xc.getARGBHex().substring(2) + ";");
                // 字体颜色
            }
            if(xf.getItalic()){
                sb.append("font-style:italic;");
            }else{
                sb.append("font-style:normal;");
            }
            if(xf.getUnderline() == 0){
                sb.append("text-decoration: none;");
            }else if(xf.getUnderline() ==1){
                sb.append("text-decoration: underline;");
            }
            sb.append("font-size:"+xf.getFontHeightInPoints()+"pt;");
            sb.append("font-weight:"+xf.getBoldweight()+";");
            sb.append("font-family:"+xf.getFontName()+";\"");
        }else if (wb instanceof HSSFWorkbook){
            HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb);
            short fontColor = hf.getColor();
            HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
            // 类HSSFPalette用于求的颜色的国际标准形式
            HSSFColor hc = palette.getColor(fontColor);
            String fontColorStr = convertToStardColor(hc);
            if (fontColorStr != null && !"".equals(fontColorStr.trim())) {
                sb.append("color:" + fontColorStr + ";");
                // 字体颜色
            }
            if(hf.getItalic()){
                sb.append("font-style:italic;");
            }else{
                sb.append("font-style:normal;");
            }
            if(hf.getUnderline() == 0){
                sb.append("text-decoration: none;");
            }else if(hf.getUnderline() ==1){
                sb.append("text-decoration: underline;");
            }
            sb.append("font-size: "+hf.getFontHeightInPoints()+"pt;");
            sb.append("font-weight: "+hf.getBoldweight()+";");
            sb.append("font-family: "+hf.getFontName()+";\"");
        }

    }
    /**
     * 处理xlsx格式
     * @param sb
     * @param cellStyle
     * @param cell
     * @param sheet
     * @return
     */
    private static StringBuffer getByXssf(StringBuffer sb, CellStyle cellStyle, Cell cell, Sheet sheet) {
        XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont();
        short boldWeight = xf.getBoldweight();
        sb.append("style='");
        int topRow = cell.getRowIndex(), topColumn = cell.getColumnIndex();
        if (map[0].containsKey(topRow + ConstantClassField.COMMA + topColumn)) {
            //该单元格为合并单元格，宽度需要获取所有单元格宽度后合
            String value = (String) map[0].get(topRow + "," + topColumn);
            String[] ary = value.split(",");
            int bottomColumn = Integer.parseInt(ary[1]);
            int bottomRow = Integer.parseInt(ary[0]);
            if (topColumn != bottomColumn) {
                //合并列，需要计算相应宽度
                int columnWidth = 0;
                for (int i = topColumn; i <= bottomColumn; i++) {
                    columnWidth += sheet.getColumnWidth(i);
                }
                sb.append("width:" + columnWidthChangePt(columnWidth) + "pt;");
            } else {
                int columnWidth = sheet.getColumnWidth(topColumn);
                sb.append("width:" + columnWidthChangePt(columnWidth) + "pt;");
            }
            if (topRow != bottomRow) {
                //合并行，需要计算相应宽度
                int rowHeight = 0;
                for (int i = topRow; i <= bottomRow; i++) {
                    rowHeight += sheet.getRow(i).getHeight();
                }
                sb.append("Height:" + rowHeightChangePt(rowHeight) + "pt;");
            } else {
                int rowHeight = sheet.getRow(topRow).getHeight();
                sb.append("Height:" + rowHeightChangePt(rowHeight) + "pt;");
            }
            sb.append(getLeftBorderStyle(sheet.getRow(topRow).getCell(topColumn).getCellStyle()));
            sb.append(getRightBorderStyle(sheet.getRow(bottomRow).getCell(bottomColumn).getCellStyle()));
        } else {
            int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());
            int rowHeight = cell.getRow().getHeight();
            sb.append("width:" + columnWidthChangePt(columnWidth) + "pt;");
            sb.append("Height:" + rowHeightChangePt(rowHeight) + "pt;");
            sb.append(getLeftBorderStyle(cellStyle));
            sb.append(getRightBorderStyle(cellStyle));
        }

        XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
        if (bgColor != null && !"".equals(bgColor.toString())) {
            sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";");
            // 背景颜色
        }
        return sb;
    }

    private static int getTotalWidth(Sheet sheet){
        int totalWidth = 0;
        int totalColumn = sheet.getRow(0).getLastCellNum();
        for(int i = 0;i<totalColumn;i++){
            totalWidth += sheet.getColumnWidth(i);
        }
        return totalWidth;
    }
    /**
     * 转换宽度
     * @param columnWidth
     * @return
     */
    private static int columnWidthChangePt(int columnWidth){
        double d = columnWidth/42.5;
        return (int) Math.round(d);
    }

    /**
     * 转换高度
     * @param rowHeight
     * @return
     */
    private static int rowHeightChangePt(int rowHeight){
        double d = rowHeight/19.95;
        return (int)Math.round(d);
    }

    /**
     * 单元格内容的水平对齐方式
     *
     * @param alignment
     * @return
     */
    private static String convertAlignToHtml(short alignment) {
        String align = "left";
        switch (alignment) {
            case CellStyle.ALIGN_LEFT:
                align = "left";
                break;
            case CellStyle.ALIGN_CENTER:
                align = "center";
                break;
            case CellStyle.ALIGN_RIGHT:
                align = "right";
                break;
            default:
                break;
        }
        return align;
    }

    /**
     * 单元格中内容的垂直排列方式
     *
     * @param verticalAlignment
     * @return
     */
    private static String convertVerticalAlignToHtml(short verticalAlignment) {
        String valign = "middle";
        switch (verticalAlignment) {
            case CellStyle.VERTICAL_BOTTOM:
                valign = "bottom";
                break;
            case CellStyle.VERTICAL_CENTER:
                valign = "center";
                break;
            case CellStyle.VERTICAL_TOP:
                valign = "top";
                break;
            default:
                break;
        }
        return valign;
    }

    private static String convertToStardColor(HSSFColor hc) {
        StringBuffer sb = new StringBuffer("");
        if (hc != null) {
            if (HSSFColor.AUTOMATIC.index == hc.getIndex()) {
                return null;
            }
            sb.append("#");
            for (int i = 0; i < hc.getTriplet().length; i++) {
                sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i])));
            }
        }
        return sb.toString();
    }

    private static String fillWithZero(String str) {
        if (str != null && str.length() < ConstantClassField.NUM2) {
            return "0" + str;
        }
        return str;
    }

    /**
     * 左上角的边框线
     * @param cellStyle
     * @return
     */
    private static String getLeftBorderStyle(CellStyle cellStyle){
        StringBuilder sb = new StringBuilder();
        if(cellStyle.getBorderTop()!=0){
            sb.append("border-top: .5pt solid black;");
        }
        if(cellStyle.getBorderLeft()!=0){
            sb.append("border-left: .5pt solid black;");
        }
        return new String(sb);
    }

    /**
     * 右下角的边框线
     * @param cellStyle
     * @return
     */
    private static String getRightBorderStyle(CellStyle cellStyle){
        StringBuilder sb = new StringBuilder();
        if(cellStyle.getBorderRight()!=0){
            sb.append("border-right: .5pt solid black;");
        }
        if(cellStyle.getBorderBottom()!=0){
            sb.append("border-bottom: .5pt solid black;");
        }
        return new String(sb);
    }


    @SuppressWarnings("unused")
    private static void writeFile(String content, String path) {
        OutputStream os = null;
        BufferedWriter bw = null;
        try {
            File file = new File(path);
            os = new FileOutputStream(file);
            bw = new BufferedWriter(new OutputStreamWriter(os, "GBK"));
            bw.write(content);
        } catch (FileNotFoundException fnfe) {
            fnfe.printStackTrace();
        } catch (IOException ioe) {
            ioe.printStackTrace();
        } finally {
            try {
                if (null != bw) {
                    bw.close();
                }
                if (null != os) {
                    os.close();
                }
            } catch (IOException ie) {
                ie.printStackTrace();
            }
        }
    }


    /**
     * 获取Excel图片公共方法
     *
     * @param sheetNum 当前sheet编号
     * @param sheet    当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     */
    public static Map<String, PictureData> getSheetPictrues(int sheetNum, Sheet sheet, Workbook workbook) {
        if (workbook instanceof HSSFWorkbook) {
            return getSheetPictrues03(sheetNum, (HSSFSheet) sheet, (HSSFWorkbook) workbook);
        } else if (workbook instanceof XSSFWorkbook) {
            return getSheetPictrues07(sheetNum, (XSSFSheet) sheet, (XSSFWorkbook) workbook);
        } else {
            return null;
        }
    }

    /**
     * 获取Excel2003图片
     *
     * @param sheetNum 当前sheet编号
     * @param sheet    当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     * @throws IOException
     */
    private static Map<String, PictureData> getSheetPictrues03(int sheetNum,
                                                               HSSFSheet sheet, HSSFWorkbook workbook) {

        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(16);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                shape.getLineWidth();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + String.valueOf(anchor.getRow1()) + "_"
                            + String.valueOf(anchor.getCol1());
                    sheetIndexPicMap.put(picIndex, picData);
                }
            }
            return sheetIndexPicMap;
        } else {
            return null;
        }
    }

    /**
     * 获取Excel2007图片
     *
     * @param sheetNum 当前sheet编号
     * @param sheet    当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     */
    private static Map<String, PictureData> getSheetPictrues07(int sheetNum,
                                                               XSSFSheet sheet, XSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(16);

        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + ctMarker.getRow() + "_" + ctMarker.getCol();
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());
                }
            }
        }

        return sheetIndexPicMap;
    }

    public static void printImg(List<Map<String, PictureData>> sheetList) throws IOException {
        for (Map<String, PictureData> map : sheetList) {
            printImg(map);
        }
    }

    public static void printImg(Map<String, PictureData> map) throws IOException {
        Object[] key = map.keySet().toArray();
        for (int i = 0; i < map.size(); i++) {
            // 获取图片流
            PictureData pic = map.get(key[i]);
            // 获取图片索引
            String picName = key[i].toString();
            // 获取图片格式
            String ext = pic.suggestFileExtension();

            byte[] data = pic.getData();

            FileOutputStream out = new FileOutputStream("D:\\pic" + picName + "_" + ext);
            out.write(data);
            out.flush();
            out.close();
        }
    }

    private static int[] getColsOfTable(Sheet sheet) {

        int[] data = {0, 0};
        for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
            if (null != sheet.getRow(i)) {
                data[0] = sheet.getRow(i).getLastCellNum();
                data[1] = sheet.getRow(i).getHeight();
            } else {
                continue;
            }
        }
        return data;
    }
}
